/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.myapp.struts.dao;

//~--- non-JDK imports --------------------------------------------------------
import com.myapp.struts.beans.AccountHeadBean;
import com.myapp.struts.beans.JvBean;
import com.myapp.struts.beans.PaymentVoucherBean;
import com.myapp.struts.beans.ReceiptsVoucherBean;

//~--- JDK imports ------------------------------------------------------------

import com.myapp.struts.prepare.PrepareLedger;
import java.sql.Connection;
import java.sql.PreparedStatement;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author pavan
 */
public class LedgerDao {

    private String acheadtype;
    private Connection con;
    private Connect drv;
    private String openingBalance;
    private PreparedStatement pstmt;
    private String query;
    private int result;
    private boolean status;
    private ResultSet rs;
    private PrepareLedger plb;
    private Vector al;
    private double crTotal, crTotal1;
    private String query1;
    private PreparedStatement pstmt1;
    private ResultSet rs1;
    private double drTotal, drTotal1;
    private double total;
    private double cb;
    private Vector closing;
    private int result1;

    public LedgerDao() {
        plb = new PrepareLedger();
        closing = new Vector();
    }

    public Connection getConnect() throws Exception {
        drv = new Connect();
        con = drv.mySql();
        return con;
    }

    public String insert(String accountHead1, String date, String date1) {
        try {
            AcheadDao imdao = new AcheadDao();
            ArrayList gp = imdao.acheadDetails(accountHead1);

            /** ************************************************Start of Pre-requisits********************* */
            /** Selected Account Head */
            if (gp != null) {
                Iterator itin = gp.iterator();

                while (itin.hasNext()) {
                    AccountHeadBean gb = (AccountHeadBean) itin.next();

                    /** Selected Account Head  Opening balance and entry type */
                    acheadtype = gb.getDescription();
                    openingBalance = gb.getOBalance();
                    System.out.println("openingBalance=" + openingBalance);
                }
            }

            /** *********************************** */
            /** ******************Journal Voucher********* */
            String jvno = "",
                    amountType = "",
                    rupeesc = "",
                    rupeesd = "";
            Vector jno = new Vector();
            Vector amtyp = new Vector();
            Vector rpcr = new Vector();
            Vector rpdt = new Vector();
            JvDao jvdao = new JvDao();
            Vector jv = jvdao.getTransactionType(accountHead1, accountHead1);

            if (jv != null) {
                System.out.println("Not Null rececipts");

                Iterator itg3 = jv.iterator();

                while (itg3.hasNext()) {
                    JvBean jb = (JvBean) itg3.next();

                    amountType = jb.getAmount_type();
                    rupeesc = jb.getCredit_Amount();
                    rupeesd = jb.getDebit_Amount();
                    jvno = jb.getJv_No();
                    jno.add(jvno);
                    amtyp.add(amountType);
                    rpcr.add(rupeesc);
                    rpdt.add(rupeesd);
                }
            }


            /** ************************************************End of Prerequisits********************* */
            /** ************************************************Start Of Debit SIDE********************* */
            /** Insert */
            double totalBalance = 0.00,
                    totalBalance1 = 0.00;
            String receiptDate = "",
                    amount = "",
                    narration = "",
                    accountHead = "";
            ReceiptsVoucerDao rvdao = new ReceiptsVoucerDao();
            ArrayList rv = rvdao.getListByAccountHead(accountHead1);

            if (rv != null) {
                Iterator itg = rv.iterator();

                while (itg.hasNext()) {
                    ReceiptsVoucherBean gb = (ReceiptsVoucherBean) itg.next();

                    accountHead = gb.getReceipt_type();
                    receiptDate = gb.getReceipt_Date();
                    amount = gb.getAmount();
                    totalBalance = totalBalance + Double.parseDouble(amount);
                    narration = gb.getNarration();

                    /** insert into  */
                    status = insert(receiptDate, accountHead, "RV", amount, "Dr");
                    /** ************ */
                }
            } else {
                System.out.println("rececipts else case");

                ArrayList rv1 = rvdao.getListByReceiptType(accountHead1);

                if (rv1 != null) {
                    Iterator itg1 = rv1.iterator();

                    while (itg1.hasNext()) {
                        System.out.println("Not Null -R");

                        ReceiptsVoucherBean gb1 = (ReceiptsVoucherBean) itg1.next();

                        accountHead = gb1.getAccount_head();
                        receiptDate = gb1.getReceipt_Date();
                        amount = gb1.getAmount();
                        totalBalance = totalBalance + Double.parseDouble(amount);
                        narration = gb1.getNarration();

                        /** ***Insert into*** */
                        status = insert(receiptDate, accountHead, "RV", amount, "Dr");

                        /** ***************** */
                    }
                }
            }

            /** *****Journal Voucher Dr*** */
            String jvdate = "",
                    amountR = "";

            for (int i = 0; i < jno.size(); i++) {
                if (amtyp.elementAt(i).equals("Dr")) {
                    System.out.println("***************** Dr" + amountR);

                    JvDao jvdao1 = new JvDao();
                    Vector jv1 = jvdao1.getAccountHeadsList(accountHead1, "Cr", jno.elementAt(i).toString());

                    if (jv1 != null) {
                        System.out.println("Not Null rececipts");

                        Iterator itg4 = jv1.iterator();

                        while (itg4.hasNext()) {
                            JvBean gb = (JvBean) itg4.next();

                            accountHead = gb.getAccount_Head();
                            jvdate = gb.getJv_Date();
                            amountR = gb.getCredit_Amount();
                            System.out.println("amount R@############" + amountR);
                            System.out.println("Dr amountn" + rpdt.elementAt(i).toString());

                            if (Double.parseDouble(rpdt.elementAt(i).toString()) > Double.parseDouble(amountR)) {
                                totalBalance = totalBalance + (Double.parseDouble(amountR));
                            } else {
                                totalBalance = totalBalance + Double.parseDouble(rpdt.elementAt(i).toString());
                            }

                            if (Double.parseDouble(rpdt.elementAt(i).toString()) > Double.parseDouble(amountR)) {
                                status = insert(jvdate, accountHead, "JV", (amountR), "Dr");
                            } else {
                                status = insert(jvdate, accountHead, "JV", (rpdt.elementAt(i).toString()), "Dr");
                            }
                        }
                    }
                }
            }

            /** ************************************************Journal Voucher Dr******************** */
            /** ************************************************END Of Debit SIDE********************* */
            /** ***********************************Start of Credit Side************************* */
            /** ***********************************1.paymentVoucher******************************* */
            String paymentDate = "";
            PaymentsVoucerDao pvdao = new PaymentsVoucerDao();
            ArrayList pv = pvdao.getPaymentsList(accountHead1);

            if (pv != null) {
                Iterator itg = pv.iterator();

                while (itg.hasNext()) {
                    System.out.println("Not Null Payments");

                    PaymentVoucherBean pvb = (PaymentVoucherBean) itg.next();

                    accountHead = pvb.getPayment_type();
                    paymentDate = pvb.getPayment_date();
                    amount = pvb.getAmount();
                    totalBalance1 = totalBalance1 + Double.parseDouble(amount);
                    narration = pvb.getNarration();
                    status = insert(paymentDate, accountHead, "PV", amount, "Cr");
                }
            } else {
                ArrayList pv1 = pvdao.getPaymentsListByType(accountHead1);

                if (pv1 != null) {
                    Iterator itg2 = pv1.iterator();

                    while (itg2.hasNext()) {
                        System.out.println("Not Null -P");

                        PaymentVoucherBean pvb1 = (PaymentVoucherBean) itg2.next();

                        accountHead = pvb1.getAccount_head();
                        paymentDate = pvb1.getPayment_date();
                        amount = pvb1.getAmount();
                        totalBalance1 = totalBalance1 + Double.parseDouble(amount);
                        narration = pvb1.getNarration();
                        status = insert(paymentDate, accountHead, "PV", amount, "Cr");
                    }
                }
            }

            /** *********************Payment Voucher End************************************ */
            String amountRs = "";

            for (int j = 0; j < jno.size(); j++) {
                if (amtyp.elementAt(j).equals("Cr")) {
                    JvDao jvdao2 = new JvDao();

                    System.out.println("--------------------" + j);

                    Vector jv1 = jvdao2.getAccountHeadsList(accountHead1, "Dr", jno.elementAt(j).toString());

                    if (jv1 != null) {
                        Iterator itg4 = jv1.iterator();

                        while (itg4.hasNext()) {
                            System.out.println("Not Null rececipts !!!!!!!!");

                            JvBean gb = (JvBean) itg4.next();

                            accountHead = gb.getAccount_Head();
                            jvdate = gb.getJv_Date();
                            amountRs = gb.getDebit_Amount();

                            if (Double.parseDouble(rpcr.elementAt(j).toString()) > Double.parseDouble(amountRs)) {
                                totalBalance1 = totalBalance1 + (Double.parseDouble(amountRs));
                            } else {
                                totalBalance1 = totalBalance1 + Double.parseDouble(rpcr.elementAt(j).toString());
                            }

                            if (Double.parseDouble(rpcr.elementAt(j).toString()) > Double.parseDouble(amountRs)) {
                                status = insert(jvdate, accountHead, "JV", amountRs, "Cr");
                            } else {
                                status = insert(jvdate, accountHead, "JV", rpcr.elementAt(j).toString(), "Cr");
                            }
                        }
                    }
                }
            }

            /** ****************************Journal Voucher Credit Side********************************* */
            /** ***********************************End of Credit Side************************* */
            /** ************************************************Condition Checkking******************* */
            double closingBalance = 0.00, tb = 0.00;
            tb = totalBalance;
            if (status == true) {
                if (totalBalance1 > totalBalance) {
                    totalBalance = totalBalance1;
                }
                if (tb < totalBalance1) {
                    closingBalance = totalBalance1 - tb + Double.parseDouble(openingBalance);
                }
                if (totalBalance1 < tb) {
                    closingBalance = tb - totalBalance1 + Double.parseDouble(openingBalance);
                }

                System.out.println("Account Head---->" + accountHead1);
                System.out.println("Account Type---->" + acheadtype);
                System.out.println("Opening Balance---->" + Double.parseDouble(openingBalance));
                System.out.println("Closing Balance---->" + closingBalance);
                return accountHead1 + "," + acheadtype + "," + openingBalance + "," + closingBalance + ",";
            } else {
                return null;
            }
            /** ***********************************End Of Condition Checkking************************** */
        } catch (Exception ex) {
            Logger.getLogger(LedgerDao.class.getName()).log(Level.SEVERE, null, ex);
        }

        return null;
    }

    public boolean insert(String vdate, String desc, String ref, String amount, String type) {
        try {
            con = getConnect();
            query = "insert into ledger values(?,?,?,?,?)";
            pstmt = con.prepareStatement(query);
            pstmt.setString(1, vdate);
            pstmt.setString(2, desc);
            pstmt.setString(3, ref);
            pstmt.setString(4, amount);
            pstmt.setString(5, type);
            result = pstmt.executeUpdate();

            if (result > 0) {
                return true;
            } else {
                return false;
            }
        } catch (Exception ex) {
            Logger.getLogger(LedgerDao.class.getName()).log(Level.SEVERE, null, ex);
        }

        return false;
    }

    public Vector list(String achead, String entryType, String date, String date1) throws Exception {
        try {
            con = getConnect();
            query =
                    "SELECT * FROM ledger where entryType='" + entryType + "' and accountHead='" + achead + "'  and vdate>='" + date + "' and vdate<'" + date1 + "' order by vdate";

            System.out.println("AFTER THE QUERY " + query);
            pstmt = con.prepareStatement(query);

            // System.out.println("values of prepared statement  "+pstmt);
            //pstmt.setString(1, entryType);
            rs = pstmt.executeQuery();
            System.out.println("RS IS ..................." + rs);
            al = plb.prepareTypeList(rs);
            System.out.println("al is not null" + al);
            rs.close();
            pstmt.close();
            con.close();
        } // end of try
        catch (Exception e) {
            System.out.println("\n exception at retrieving the data" + e);
        }    // end of catch

        return al;
    }

    public Vector getTotalAndClosing(String openingBalance, String accountHead) throws Exception {
        try {
            con = getConnect();
            // System.out.println(con);

            query = "SELECT sum(amount) FROM ledger where accounthead='" + accountHead + "' and entryType='Cr';";
            System.out.println("AFTER THE QUERY " + query);
            pstmt = con.prepareStatement(query);
            rs = pstmt.executeQuery();
            if (rs.next() == true) {
                crTotal = rs.getDouble(1);
            }

            query1 = "SELECT sum(amount) FROM ledger where accounthead='" + accountHead + "' and entryType='Dr';";
            System.out.println("AFTER THE QUERY " + query1);
            pstmt1 = con.prepareStatement(query1);
            rs1 = pstmt1.executeQuery();
            if (rs1.next() == true) {
                drTotal = rs1.getDouble(1);
            }
            System.out.println("cr=" + crTotal);
            System.out.println("dr=" + drTotal);
            crTotal1 = crTotal;
            drTotal1 = drTotal;
            if (crTotal > drTotal) {
                cb = crTotal - drTotal;
                total = crTotal;
            } else {
                cb = drTotal - crTotal;
                total = drTotal;
            }
            closing.add(crTotal1);
            closing.add(drTotal1);
            closing.add(total);
            closing.add(cb);
            rs.close();
            rs1.close();
            pstmt.close();
            con.close();
        } // end of try
        catch (Exception e) {
            System.out.println("\n exception at retrieving the data" + e);
        }    // end of catch

        return closing;
    }

    public boolean deleteAll() throws Exception {
        try {
            con = getConnect();
            // System.out.println(con);
            query = "delete from ledger";
            pstmt = con.prepareStatement(query);

            // System.out.println("values of prepared statement  "+pstmt);
            //pstmt.setString(1, entryType);
            result1 = pstmt.executeUpdate();
            pstmt.close();
            con.close();
            if (result1 > 0) {
                return true;
            } else {
                return false;
            }
        } // end of try
        catch (Exception e) {
            System.out.println("\n exception at retrieving the data" + e);
        }    // end of catch

        return false;
    }

    public Vector trialBalance(String entryType, String date, String date1) throws Exception {
        try {
            con = getConnect();
            query =
                    "SELECT * FROM ledger where entryType='" + entryType + "' and referance!='OB' and " +
                    " vdate>='" + date + "' and vdate<'" + date1 + "' order by vdate Desc Limit 1";

            System.out.println("AFTER THE QUERY " + query);
            pstmt = con.prepareStatement(query);            
            rs = pstmt.executeQuery();
            al = plb.prepareTypeList(rs);                            
            System.out.print("al ............."+al.size());
            rs.close();
            pstmt.close();
            con.close();
        } // end of try
        catch (Exception e) {
            e.printStackTrace();
            System.out.println("\n exception at retrieving the data" + e);
        }    // end of catch

        return al;
    }

    public double getTrialBalanceTotal(String Amttype, String frmDate, String toDate) throws Exception {
        try {
            con = getConnect();
            // System.out.println(con);

            query = "SELECT sum(closingbalance) FROM ledger " +
                    "where entryType='" + Amttype + "' and referance!='OB' and " +
                    "vdate>='" + frmDate + "' and vdate<'" + toDate + "' order by vdate Desc;";
            System.out.println("AFTER THE QUERY " + query);
            pstmt = con.prepareStatement(query);
            rs = pstmt.executeQuery();
            if (rs.next() == true) {
                total = rs.getDouble(1);
            }

            rs.close();            
            pstmt.close();
            con.close();
        } // end of try
        catch (Exception e) {
            e.printStackTrace();
            System.out.println("\n exception at retrieving the data" + e);
        }    // end of catch

        return total;
    }

    public Vector getOpeningBalanceLedger(String entryType, String date, String date1) throws Exception {
        try {
            con = getConnect();
            query =
                    "SELECT * FROM ledger where entryType='" + entryType + "' and referance='OB' and " +
                    " vdate>='" + date + "' and vdate<'" + date1 + "' order by vdate";

            System.out.println("AFTER THE QUERY " + query);
            pstmt = con.prepareStatement(query);
            rs = pstmt.executeQuery();
            if (rs.next()==true)
                al = plb.prepareTypeList(rs);

            rs.close();
            pstmt.close();
            con.close();
        } // end of try
        catch (Exception e) {
            e.printStackTrace();
            System.out.println("\n exception at retrieving the data" + e);
        }    // end of catch

        return al;
    }

}
